Using the Loan Loss Reserve utility

This planning utility provides a standard method for budgeting or forecasting the allowance for loan loss and monthly provision expense.

The utility applies loss coverage rates to user-defined categories of loans to project required reserve balances. Axiom calculates provision expense as the projected change in those required reserve balances, adjusted for forecasted monthly charge offs and recoveries. Using the utility, you can enter estimates of loss reserves against specific loans or groups of loans, if significant, and to segment loans into risk categories.

Axiom generates and saves reserves and provisions for every organization unit you select. This will likely be by company or some other top-level grouping. Depending on the setup, you can save projected financial balances and expenses in total for an organizational unit or by loan category within each organizational unit.

IMPORTANT: Before using this utility, make sure to configure or edit the Loan Loss Category driver. You will not be able to process this utility until the driver is configured.

The utility is comprised of two main sections: Summary and Category.

Summary sections

The summary section displays actual and projected data for the total organization when you run the utility for all your institution's companies; otherwise it displays a total of only the companies you selected.

The summary sections include data rolled up from the category sections. They report on historical and projected balances and expense only, and provide a way to check the validity of the projection at each level.

Each summary section is followed by the category sections for every active category in the company.

Category sections

The category sections are where you enter data, if needed, and where Axiom performs its calculations. All results are saved to the database from these sections.

Axiom queries the actual data category for balances and provision expense. The system then calculates ratios and the subtotal lines. Then, queried records are loaded into hidden lines at the bottom of each category section and Sumif formulas populate the historical data in each line of the schedule.

There are two types of category sections: parent and child. In all cases, the parent is listed first followed by its children. There are only a few differences between a parent and child category section:

Child loan balances are subtracted from parent loan balances. This is shown in the example below where the loan balance for parent category: Commercial drops in November and then jumps back up in December due to the loan balances entered for child category: Lease.
Every line where there are child category balances is subtracted from the parent for actual base periods. If the Lease category contained general reserves or provision expense in actual periods, those amounts would be subtracted from the Commercial category. Other than loan balances, the calculated values for projected months are not netted since all other lines are calculated from loan balances or entered for that category.
Finally, you can enter projected loan balances manually in child categories. In the following example, lease balances were not budgeted separately but were included in commercial loans or else there would be projected lease balances. Entering balances manually allows you to break out loan balances that might only be available in the loan system.

Click image to view full size

The Commercial parent loan category reflects the changes in the loan balances of the Lease child category

The following table describes the rows in each section:

Row	Description
Loan balance	The gross loan balances queried from the target budget plan table and budgeted/forecasted in the budget and/or projection utilities.
Specific reserve balance	The amount calculated using input from the Specific reserves added and Specific reserves written off rows.
Loan balances covered by general reserve	The loan balances, net of specific reserves.
Coverage ratio	The values from the Loan Loss Category driver. You can enter different rates here or modify the values in the driver.
General reserves target balance	The loan balances covered by general reserve multiplied by the coverage ratio. The result is set to negative.
Change in general reserves balance	The increase or decrease in the general reserves target balance from the previous month.
Average loan balances covered by general reserve	Equal to the loan balances covered by general reserve (actual months are actual).
Charge off ratio	Uses the same treatment as the coverage ratio. Initially, this value come from the Loan Loss Reserve driver, which you can edit, if needed.
Monthly charge offs	The average loan balances covered by general reserve target balance times the charge off ratio.
Recovery ratio	Uses the same treatment as the coverage and charge off ratios. Initially, this values comes from the Loan Loss Reserve driver, which you can edit, if needed.
Monthly recoveries	The average loan balances covered by general reserve target balance multiplied by the recovery ratio. The result is set to negative.
Net charge offs	The monthly charge off net of recoveries.
Specific reserved added (provision expense)	Manually entered value.
Specific reserves written off	Manually entered value.
General provision expense	The change in general reserves balance plus net charge offs. Since provision expense is calculated from the change in negative balances, it is set to a positive number. Charge offs reduce the general reserve balance and must be offset with provision expense to meet the general reserves target balance.
General reserves	The general reserve's target balance less the charged off balances plus the recovered balance.
Charged off balances	The previous month's charged off balance plus the current period’s monthly charge offs.
Recovered balances	The previous month's recovered balance plus the current period’s monthly recoveries.

Axiom saves all of the input data and results for each category as statistical records. This data is not only available for reporting, but the following input values are reloaded into the Category sections whenever you refresh the utility:

Loan balances: As manually entered in child categories.
Specific reserves: Axiom derives the base period from the first projection period if it is not available as a General Ledger (GL) balance.
Coverage ratio, charge off ratio, and recovery ratio: These are the same as the Loan Loss category drive table values unless they were changed by the user.
Specific reserves added (provision expense).
Specific reserves written off.

This allows you to refresh and save each company individually, without losing the input. You can also enter these inputs and then process the utility in the Plan Utilities tab of the budget to generate updated reserves and expense as budgeted loan balances change.

Posting loan loss reserves to the database

To post loan loss reserves to the database:

Open a budget, scenario, or forecast.
From the Plan Utilities tab, click Loan Loss Reserve Utility from the list of planning utilities.

NOTE: The utility opens in the Desktop Client.
In the Main ribbon tab, click Refresh.

TIP: When refreshing the utility, you have the option to select one or more of the companies. You can load one company at a time, update as needed, and then save the results. Optionally, to load all companies, leave the Company selection blank in the Refresh Variables dialog.
In the Coverage ratio, Charge off ratio, and Recovery ratio rows, change the default values taken from the Loan Loss Category driver table, if needed.

Click image to view full size
In the Specific reserves balance row for the category, enter the amount as a negative number.

NOTE: Generally, these types of reserves are not available as separate accounts in the GL; however, if they are available and their account is mapped to the Specific Reserve Loan Loss Reserve type in the ACCT dimension, Axiom will make the historical balances available.

Click image to view full size
In the Specific reserves added (provision expense) row, enter additions to specific reserves as positive numbers. These are provisions for losses, or actual losses taken, and are mapped to an expense account.

Click image to view full size
In the Specific reserves written off row, enter any projected write-offs of the specific reserves as positive numbers. These are not expense but are loan balances charged off against specific reserves taken.

TIP: Axiom will likely recognize projected recoveries of loans that were written off against specific reserves as income, so enter these amounts as a negative expense.

Click image to view full size
To save and post your changes to the database, in the Main ribbon tab, click Save.
Close the Loan Loss Reserve utility.

See the following section to process and apply the data to the budget.

Processing loan loss reserves and provisions for a budget

To process loan loss reserves and provisions for a budget:

Open a budget, scenario, or forecast.
Click the Plan Utilities tab.
Do one of the following:
- To process only this utility, in the Process Plan Utilities column, locate Loan Loss Reserves Utility in the list, and click Process.
- To process all planning utilities, click Process All.

TIP: Axiom will notify you when your scheduled job is completed. for more information, see Viewing notifications in the Web Client.